package com.navi.core.util;

import com.navi.mould1.exception.JnReportErrorException;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtil {


	private static final int CONTENT_INDEX = 1;

	public static Sheet loadExcel(InputStream inStream) {

		Sheet sheet = null;
		try {
			Workbook workBook = new XSSFWorkbook(inStream);
			sheet = workBook.getSheetAt(0);
			if (sheet == null) {
				throw new JnReportErrorException("上传的EXCEL中没有标签页,错误!!");  //【要编写异常类】
			}
		} catch (Exception e) {
			throw new JnReportErrorException(e);//【要编写异常类】
		} finally {
			try {
				if (inStream != null) {
					inStream.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return sheet;
	}

	public static List<String> getExcelHeaders(List<List<String>> resultList) {
		return resultList.get(0);
	}

	public static List<List<String>> getExcelResult(InputStream inStream) {
		Sheet sheet = loadExcel(inStream);
		int rowNum = sheet.getLastRowNum();
		List<List<String>> resultList = new ArrayList<List<String>>();
		for (int i = 0; i < rowNum + 1; i++) {
			Row row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			List<String> result = new ArrayList<String>();
			result.add(String.valueOf(i+1));
			for (int j = 0; j < row.getLastCellNum(); j++) {
				result.add(getCellStringValue(row,j));
			}
			
			if(StringUtils.isEmpty(getCellStringValue(row,0)) && StringUtils.isEmpty(getCellStringValue(row,1))
					&& StringUtils.isEmpty(getCellStringValue(row,2))){
				continue;
			}

			resultList.add(result);
		}
		return resultList;
	}
	
	public static String getCellStringValue(Row row, int index){
		Cell cell = row.getCell(index);
		String str = getCellValue(cell);
		return str;
	}
	public static List<List<String>> getExcelContent(List<List<String>> resultList) {
		return resultList.subList(CONTENT_INDEX, resultList.size());
	}

	public static String getCellValue(Cell cell) {
		String cellValue = "";
		DataFormatter formatter = new DataFormatter();
		if (cell != null) {
			// 判断单元格数据的类型，不同类型调用不同的方法
			switch (cell.getCellType()) {
			// 数值类型
			case Cell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
					SimpleDateFormat sdf = null;
					if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
						sdf = new SimpleDateFormat("HH:mm");
					} else {// 日期
						sdf = new SimpleDateFormat("yyyy-MM-dd");
					}
					Date date = cell.getDateCellValue();
					cellValue = sdf.format(date);
				} else if (cell.getCellStyle().getDataFormat() == 58) {
					// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					double value = cell.getNumericCellValue();
					Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
					cellValue = sdf.format(date);
				} else {
					// double value = cell.getNumericCellValue();
					// CellStyle style = cell.getCellStyle();
					// DecimalFormat format = new DecimalFormat();
					// String temp = style.getDataFormatString();
					// // 单元格设置成常规
					// if (temp.equals("General")) {
					// format.applyPattern("#");
					// }
					// cellValue = format.format(value);

					double f = cell.getNumericCellValue();
					BigDecimal b = new BigDecimal(f);
					double value = b.setScale(2,RoundingMode.HALF_UP).doubleValue();//四舍五入只保留一位
					int intValue = (int) value;
					cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
				}
				break;
			case Cell.CELL_TYPE_STRING:
				cellValue = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				cellValue = String.valueOf(cell.getBooleanCellValue());
				break;
			// 判断单元格是公式格式，需要做一种特殊处理来得到相应的值
			case Cell.CELL_TYPE_FORMULA: {
				try {
					cellValue = String.valueOf(cell.getNumericCellValue());
				} catch (IllegalStateException e) {
					cellValue = String.valueOf(cell.getRichStringCellValue());
				}

			}
				break;
			case Cell.CELL_TYPE_BLANK:
				cellValue = "";
				break;
			case Cell.CELL_TYPE_ERROR:
				cellValue = "";
				break;
			default:
				cellValue = cell.toString().trim();
				break;
			}
		}
		return cellValue.trim();
	}

	public static void main(String[] args) throws FileNotFoundException {
		InputStream inputStrean = new FileInputStream(new File("E:/Move In申请单导入- 比较之后.xlsx"));
		List<List<String>> excelContentList = getExcelResult(inputStrean);
		List<List<String>> excelContent = getExcelContent(excelContentList);
		for (List<String> list : excelContent) {
			for (String string : list) {
				System.out.print(string);
			}
			System.out.println("");
		}
	}

}
